package JDBC_test;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.*;
import java.util.UUID;


public class mysql8 {
    private final String DRIVER = "com.mysql.cj.jdbc.Driver";// Oracle的驱动
    private final static String URL = "jdbc:mysql://192.168.1.109:3306/renfangpingtai?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true";// Oracle9i为Oracle的主机串名（也就是Oracle的SID）在装Oracle的时候填写的SID
    private final static String DBUSER = "root";// 这里就以scott/tiger为用户登录
    private final static String DBPASSWORD = "123";
    private static Connection con = null;// 预先定义一个连接接口
    static Statement statement=null;
    /*
    public App() {// 在构造方法中加载数据库驱动
        try {
            Class.forName(DRIVER).newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
     */
    public static Connection getConnection() { // 连接数据库的方法
        try {
            if(con==null) {
                con = DriverManager.getConnection(URL, DBUSER, DBPASSWORD);
            }

        } catch (Exception ew) {
            ew.printStackTrace();
        }

        return con;
    }

    public static JSONArray list(String sql) {
        System.out.println(sql);
        JSONArray result=new JSONArray();
        try {
            getConnection();
            if(statement==null) {
                statement=con.createStatement();
            }
            result = convertResultSetToJsonArr(statement.executeQuery(sql));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int execute(String sql) {
        System.out.println(sql);
        int result=0;
        try {
            getConnection();
            if(statement==null) {
                statement=con.createStatement();
            }
            result = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static void txtToOracle() {
        File file = new File(System.getProperty("user.dir")+File.separator+"text1.txt");
        BufferedReader reader = null;
        JSONArray ja=new JSONArray();
        try {
            reader = new BufferedReader(new FileReader(file));
            String tempStr;
            while ((tempStr = reader.readLine()) != null) {
                String[] ss1=tempStr.split(" ");

                String c1=ss1[0];
                String c2=ss1[1];
                String c3=ss1[2];
                String[] ss2=ss1[3].split("-");
                String c5=ss1[4].equals("入")?"1":"2";
                String c6=ss1[5].equals("闭")?"1":"2";
                String c7=ss1[6].equals("赚")?"1":"2";
                for (int i = 0; i < ss2.length; i++) {
                    String c4=ss2[i];
                    ja=list("select \"id\",\"biaoshi\" from \"table1\" where \"id\"='"+c4+"'");
                    if(ja.size()>0) {
                        c4=ja.getJSONObject(0).getString("biaoshi");
                    }
                    String sql="INSERT INTO \"MYTEST\".\"t1\" VALUES ('"+UUID.randomUUID()+"', '"+c1+"', '"+c2+"', '"+c3+"', '"+c4+"', '"+c5+"', '"+c6+"', '"+c7+"')";
                    execute(sql);
                }
            }
            reader.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (Exception e1) {
                    e1.printStackTrace();
                }
            }
        }
    }

  


    public static JSONArray convertResultSetToJsonArr(ResultSet rs) throws SQLException, JSONException {
        JSONArray json = new JSONArray();
        ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            int numColumns = rsmd.getColumnCount();
            JSONObject obj = new JSONObject();

            for (int i = 1; i < numColumns + 1; i++) {
                String column_name = rsmd.getColumnName(i);

                if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
                    obj.put(column_name, rs.getArray(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
                    obj.put(column_name, rs.getBoolean(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
                    obj.put(column_name, rs.getBlob(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
                    obj.put(column_name, rs.getDouble(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
                    obj.put(column_name, rs.getFloat(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
                    obj.put(column_name, rs.getNString(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
                    obj.put(column_name, rs.getString(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
                    obj.put(column_name, rs.getInt(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
                    obj.put(column_name, rs.getDate(column_name));
                } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
                    obj.put(column_name, rs.getTimestamp(column_name));
                } else {
                    obj.put(column_name, rs.getObject(column_name));
                }
            }

            json.add(obj);
        }

        return json;
    }
    
    
    
    
    
    public static void main(String[] args) {
    	String sql="SELECT s1.* FROM(\n" +
                "\tSELECT yidijianshefei_lishiqianjiaozhuijiao_bl.*,v_quanbugongchengjibenshuxing_bf.`GCMC`,v_quanbugongchengjibenshuxing_bf.`GCWZ`,v_quanbugongchengjibenshuxing_bf.`DSMC`,v_quanbugongchengjibenshuxing_bf.`QXMC` FROM `yidijianshefei_lishiqianjiaozhuijiao_bl`\n" +
                "\tLEFT JOIN `yidijianshefei_lishiqianjiaozhuijiao_jd` ON yidijianshefei_lishiqianjiaozhuijiao_bl.`JDID`=yidijianshefei_lishiqianjiaozhuijiao_jd.id\n" +
                "\tLEFT JOIN v_quanbugongchengjibenshuxing_bf ON yidijianshefei_lishiqianjiaozhuijiao_bl.`GCID`=v_quanbugongchengjibenshuxing_bf.`Id`\n" +
                ") s1\n" +
                "WHERE 1=1 ";
        System.out.println(list(sql));
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
}
